package com.yihu.wlyy.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.yihu.wlyy.entity.SignWeb;

public interface SignWebDao extends PagingAndSortingRepository<SignWeb, Long>, JpaSpecificationExecutor<SignWeb> {

	// 根據CODE查詢咨询记录
	SignWeb findByCode(String code);

	// 根据医生信息获取签约列表 已解约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (1,5) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findCancelledSignWebByDoctor(String doctor, long id, Pageable pageRequest);

	// 根据医生信息获取签约列表 待签约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (0) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findToBeSignSignWebByDoctor(String doctor, long id, Pageable pageRequest);

	// 根据医生信息获取签约列表 已签约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (2) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findSignedSignWebByDoctor(String doctor, long id, Pageable pageRequest);

	// 根据医生信息获取签约列表 待解约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (3,4) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findToCancelSignWebByDoctor(String doctor, long id, Pageable pageRequest);

	// 根据医生信息获取签约列表 已解约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (1,5) ORDER BY a.id desc")
	Page<Object> findCancelledSignWebByDoctor(String doctor, Pageable pageRequest);

	// 根据医生信息获取签约列表 待签约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (0) ORDER BY a.id desc")
	Page<Object> findToBeSignSignWebByDoctor(String doctor, Pageable pageRequest);

	// 根据医生信息获取签约列表 已签约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (2) ORDER BY a.id desc")
	Page<Object> findSignedSignWebByDoctor(String doctor, Pageable pageRequest);

	// 根据医生信息获取签约列表 待解约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.releaseSpeak from SignWeb a,Patient b where a.user=b.code and a.doctor = ?1 and a.status in (3,4) ORDER BY a.id desc")
	Page<Object> findToCancelSignWebByDoctor(String doctor, Pageable pageRequest);

	// 根据患者Code获取当前签约的医生信息
	@Query("select a from SignWeb a where a.user = ?1 and a.status!=5")
	SignWeb findSignWebByPatient(String patient);

	// 根据患者Code和查询条件查找签约信息
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id  from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 ORDER BY a.id desc")
	Page<Object> findSignInfoByPatient(String patient, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （已解约、已拒绝）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (1,5) ORDER BY a.id desc")
	Page<Object> findCancelledSignInfoByPatient(String patient, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (0) ORDER BY a.id desc")
	Page<Object> findToBedSignInfoByPatient(String patient, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (2) ORDER BY a.id desc")
	Page<Object> findSignedSignInfoByPatient(String patient, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (3,4) ORDER BY a.id desc")
	Page<Object> findToCancelSignInfoByPatient(String patient, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id  from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.id<?2 ORDER BY a.id desc")
	Page<Object> findSignInfoByPatient(String patient, long id, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （已解约、已拒绝）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (1,5) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findCancelledSignInfoByPatient(String patient, long id, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (0) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findToBedSignInfoByPatient(String patient, long id, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (2) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findSignedSignInfoByPatient(String patient, long id, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status,b.photo,a.id from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (3,4) and a.id<?2 ORDER BY a.id desc")
	Page<Object> findToCancelSignInfoByPatient(String patient, long id, Pageable pageRequest);

	// 根据患者Code和查询条件查找签约信息 （待签约）
	@Query("select a.code ,a.user ,b.code ,b.name ,b.hosptialName,b.deptName,b.jobName,a.status from SignWeb a , Doctor b where  a.doctor = b.code and a.user=?1 and a.status in (2,3,4)")
	List<Object> findSignedSignInfoBystatus(String patient);
}
